# -*- coding:utf8 -*- #
# -----------------------------------------------------------------------------------
# ProjectName: smalleel-api-tools
# FileName:   mySqlUtil.py
# Author  :
# Datetime:     2022/8/16 15:18
# Description: 连接数据库，执行sql语句
# -----------------------------------------------------------------------------------
from typing import Union

import pymysql
import sshtunnel
from dbutils.pooled_db import PooledDB
from pymysql import Connect, OperationalError
from sshtunnel import SSHTunnelForwarder

from common.util.jsonUtil.jsonUtil import read_sql_json, read_extract_json
from common.util.configUtil.configUtil import ConfigUtil

config = ConfigUtil()


class Database(object):
    """
    电表数据库连接
    """
    __pool = None

    def __init__(self):
        self._conn = None
        self.cursor = None
        self.connect = None

    def db_connect(self, dataBaseName: str):
        try:
            if config.get_database_status():
                server = sshtunnel.SSHTunnelForwarder(
                    (config.get_database_info("SSH_Proxy_host"), int(config.get_database_info("SSH_Proxy_port"))),
                    # 这里写入B 跳板机IP、端口
                    ssh_username=config.get_database_info("SSH_Proxy_user"),  # 跳板机 用户名
                    ssh_password=config.get_database_info("SSH_Proxy_password"),  # 跳板机 密码
                    remote_bind_address=(
                        config.get_database_info("Pro_host"), int(config.get_database_info("Pro_port"))),
                    # 这里写入 C数据库的 IP、端口号
                )
                server.start()
                self.connect = Connect(
                    host="127.0.0.1",  # 只能写 127.0.0.1，这是固定的，不可更改
                    port=server.local_bind_port,  # 固定可不用更改
                    user=config.get_database_info("Pro_user"),  # 数据库 用户名
                    password=config.get_database_info("Pro_password"),  # 数据库 密码
                    db=config.get_database_info(dataBaseName),  # 填写需要连接的数据库名
                    charset='utf8'
                )
                connect = self.connect
                return connect
            else:
                self.connect = Connect(user=config.get_database_info("Test_user"),
                                       password=config.get_database_info("Test_password"),
                                       host=config.get_database_info("Test_host"),
                                       port=int(config.get_database_info("Test_port")),
                                       database=config.get_database_info(dataBaseName))
                connect = self.connect  # 建立游标，通过游标执行SQL语句
                return connect
        except sshtunnel.HandlerSSHTunnelForwarderError as handlerError:
            print(f"ERROR: HandlerSSHTunnelForwarderError----{handlerError}")
        except sshtunnel.BaseSSHTunnelForwarderError as baseError:
            print(f"ERROR: BaseSSHTunnelForwarderError----{baseError}")
        except Exception as error:
            print(f"ERROR: db_connect----{error}")

    def close_connect(self):
        self.connect.close()


def choose_database(databaseNameType: int = 1) -> Connect:
    """
        :param databaseNameType: 系统类型：
                                1:web端
                                2:water
                                5:暖通空调
                                4:支付模块
    """
    dataBaseName = ""
    dataBase = Database()
    if config.get_database_status():
        if databaseNameType == 1:
            dataBaseName = "Pro_meter_database"
        elif databaseNameType == 4:
            dataBaseName = "Pro_water_database"
        elif databaseNameType == 5:
            dataBaseName = "Pro_hvac_database"
        elif databaseNameType == 6:
            dataBaseName = "Pro_third_pay_database"
        else:
            print("暂无该系统数据库！")

    else:
        if databaseNameType == 1:
            dataBaseName = "Test_meter_database"
        elif databaseNameType == 4:
            dataBaseName = "Test_water_database"
        elif databaseNameType == 5:
            dataBaseName = "Test_hvac_database"
        elif databaseNameType == 6:
            dataBaseName = "Test_third_pay_database"
        else:
            print("暂无该系统数据库！")
    return dataBase.db_connect(dataBaseName)


def select_sql(sqlQuery: str, databaseName: int = 1) -> list:
    """
        测试用例封装查询
    :param sqlQuery: 查询语句
    :param databaseName: 系统类型：
                                1:web端
                                2:小程序
                                3:商户小程序
                                4:平台
                                4:water
                                5:暖通空调
                                6:支付模块
    :return: list
    """
    try:
        if not sqlQuery.lower().startswith("select"):
            print("请使用: operation_sql() 函数！")
            return
        else:
            connect = choose_database(databaseName)
            cursor = connect.cursor()
            cursor.execute(sqlQuery)
            result = cursor.fetchall()
            allResult = []
            if len(result) == 0:
                return ""
            else:
                for i in result:
                    rowList = []
                    for k in i:
                        if k == "" or k is None:
                            k = 0
                        rowList.append(k)
                    allResult.append(rowList)
            cursor.close()
            connect.close()
            return allResult
    except OperationalError as e:
        print(e)


def operation_sql(sqlQuery: str, databaseNameType: int = 1):
    if sqlQuery.lower().startswith("select"):
        print("请使用: select_sql() 函数！")
        return
    else:
        connect = choose_database(databaseNameType)
        cursor = connect.cursor()
        cursor.execute(sqlQuery)
        connect.commit()
        connect.close()


if __name__ == '__main__':
    # d = read_sql_json('WaterInfo', 'ubindWater', 4, read_extract_json('proid', 4))
    # data = select_sql("SELECT water_no FROM water_db_info WHERE pro_id = 1631190338328903682 and base_device_no is NOT Null and is_del=0 ORDER BY RAND() LIMIT 1;", 4)
    # print(d)
    # print(select_sql(read_sql_json("Test", "delete_tasks", 5, "冷源系统", read_extract_json('proid', 5)), 5))
    print(select_sql(read_sql_json('balance_sql', 'aid', 1, read_extract_json('proid')))[0][0])

